﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace BanHangCshap.Class
{
    class DonDatHang:Database
    {
        #region ***** Fields & Properties *****
        private string _manhapxuat;
        public string manhapxuat
        {
            get
            {
                return _manhapxuat;
            }
            set
            {
                _manhapxuat = value;
            }
        }
        private string _makho;
        public string makho
        {
            get
            {
                return _makho;
            }
            set
            {
                _makho = value;
            }
        }
        private string _ngaynhapxuat;
        public string ngaynhapxuat
        {
            get
            {
                return _ngaynhapxuat;
            }
            set
            {
                _ngaynhapxuat = value;
            }
        }
        private string _ngayphaigiao;
        public string ngayphaigiao
        {
            get
            {
                return _ngayphaigiao;
            }
            set
            {
                _ngayphaigiao = value;
            }
        }
        private bool _xuatgiasi;
        public bool xuatgiasi
        {
            get
            {
                return _xuatgiasi;
            }
            set
            {
                _xuatgiasi = value;
            }
        }
        private string _makhncc;
        public string makhncc
        {
            get
            {
                return _makhncc;
            }
            set
            {
                _makhncc = value;
            }
        }
       
        private double _tongtienso;
        public double tongtienso
        {
            get
            {
                return _tongtienso;
            }
            set
            {
                _tongtienso = value;
            }
        }
        private string _tongtienchu;
        public string tongtienchu
        {
            get
            {
                return _tongtienchu;
            }
            set
            {
                _tongtienchu = value;
            }
        }
        
        
        private double _tamung;
        public double tamung
        {
            get
            {
                return _tamung;
            }
            set
            {
                _tamung = value;
            }
        }
        
       
       
        
        private string _tendn;
        public string tendn
        {
            get
            {
                return _tendn;
            }
            set
            {
                _tendn = value;
            }
        }
        private string _ghichunx;
        public string ghichunx
        {
            get
            {
                return _ghichunx;
            }
            set
            {
                _ghichunx = value;
            }
        }
        private int _id;
        public int id
        {
            get
            {
                return _id;
            }
            set
            {
                _id = value;
            }
        }
        private bool _dagiao;
        public bool dagiao
        {
            get
            {
                return _dagiao;
            }
            set
            {
                _dagiao = value;
            }
        }
        
        
        #endregion

        #region ***** Init Methods *****
        public DonDatHang()
        {
        }
        public DonDatHang(string manhapxuat)
        {
            this.manhapxuat = manhapxuat;
        }
        
        #endregion
        public Boolean isExistDonDatHang(string manhapxuat)
        {
            string sql = "select manhapxuat from DonDatHang where manhapxuat = '" + manhapxuat + "'";
            return isExist(sql);
        }
        public Boolean themDonDatHang()
        {
            string sql = "insert into DonDatHang(manhapxuat,ngaynhapxuat,ngayphaigiao,xuatgiasi,makhncc,tongtienso,tongtienchu,tamung,tendn,ghichunx,id,dagiao) ";
            sql += "values ('" + manhapxuat + "','" + ngaynhapxuat + "','" + ngayphaigiao + "','" + xuatgiasi + "','" + makhncc + "'," + tongtienso + ",N'" + tongtienchu + "'," + tamung + ",'" + tendn + "',N'" + ghichunx + "'," + id + ",'false')";
            return executeQueryTrans(sql);
            //return executeQuery(sql);
        }
       
        public Boolean xoaDonDatHang(string manhapxuat)
        {
            string sql = "delete DonDatHang where manhapxuat = '" + manhapxuat + "'";
            return executeQueryTrans(sql);
        }
        public Boolean capNhatDonDatHang(string manhapxuat, Boolean trangthai)
        {
            string sql = "update DonDatHang set dagiao = '" + trangthai + "' where manhapxuat = '" + manhapxuat + "'";
            return executeQueryTrans(sql);
        }
        public DataTable layDSDonDatHang()
        {
            string sql = "select manhapxuat,tongtienso,ngaynhapxuat, ngayphaigiao, tenkhncc from DonDatHang a left join KhachHangNCC b on a.makhncc = b.makhncc where dagiao = 'false'";
            return getDataTable(sql);
        }
        public DataTable layDSDonDatHangTrongNgay(string ngay)
        {
            string sql = "select * from DonDatHang where daxoa = 'false' and convert(varchar(10),ngaynhapxuat,103) = convert(varchar(10),'" + ngay + "')";
            return getDataTable(sql);
        }
        public DataTable layThongTinDonDatHang(string manhapxuat)
        {
            string sql = "select distinct manhapxuat,ngaynhapxuat,ngayphaigiao,xuatgiasi,makhncc,tongtienso, tamung,tongtienchu,tendn,ghichunx,id from DonDatHang where manhapxuat = '" + manhapxuat + "'    ";


            return getDataTable(sql);
        }
        public int layTongSoPhieuNhapXuat()
        {
            string sql = "select manhapxuat from PhieuNhapXuat ";
            return getDataTable(sql).Rows.Count;
        }
        
        public DataTable layDSDonDatHangCT(string manhapxuat)
        {
            //string sql = "select a.mahang, tenhang, a.dvt,slchuaquydoi,slquydoi, slnhapxuat,quycach,phivanchuyen,dgnhapxuat, chietkhau,thuexuat,thanhtien,chuthich from DonDatHangCT a, HangHoa b where a.mahang = b.mahang ";
            //sql += "and manhapxuat = '" + manhapxuat + "'  order by stt ";
            string sql = "select a.mahang, a.dvt,slchuaquydoi, isnull((select sum(slchuaquydoi) from PhieuNhapXuatCT where mahang = a.mahang and maddh = a.manhapxuat),0) as sldagiao,slquydoi, slnhapxuat,quycach,phivanchuyen,dgnhapxuat, chietkhau,thuexuat,thanhtien,chuthich  ";
            sql += "from DonDatHangCT a, HangHoa b where a.mahang = b.mahang   ";
            sql += "and manhapxuat = '" + manhapxuat + "'  order by stt";
            return getDataTable(sql);
        }


        public double laySoDonDatHangTrongNgay(string ngay)
        {
            string sql = "SELECT max(convert(int,substring(manhapxuat,13,len(manhapxuat)-11))) FROM DonDatHang WHERE convert(varchar(10),ngaynhapxuat,103)='" + ngay + "'   ";
            return getNumberValue(sql);
        }
        public DataTable timKiemDonDatHang( string tungay, string denngay, string makhncc)
        {
            string sql = "select distinct N'Kết quả tìm kiếm' as tieude,a.manhapxuat,ngaynhapxuat,ngayphaigiao,xuatgiasi,tenkhncc,tongtienso, convert(bit,(case when (select distinct count(manhapxuat) from v_DDHChiTietChuaGiao where manhapxuat = a.manhapxuat and convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)) >0 then 'False' else 'True' end)) as dagiao from DonDatHang a left join KhachHangNCC c on a.makhncc = c.makhncc where  ";
            sql += " convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            if (makhncc != "")
            {
                sql += "and a.makhncc = '" + makhncc + "' ";
            }
            sql += " order by a.manhapxuat desc";
            return getDataTable(sql);
        }
        public DataTable layDSDonDatHangChuaGiao()
        {
            string sql = "select distinct a.makhncc,a.makhncc + ' - ' + tenkhncc as khachhang,manhapxuat,ngaynhapxuat,ngayphaigiao,tongtienso, dagiao from DonDatHang a, KhachHangNCC b   ";
            sql += "where a.manhapxuat in (select distinct manhapxuat from v_DDHChiTietChuaGiao) and a.makhncc = b.makhncc ";
            sql += "order by ngayphaigiao desc";
            
            return getDataTable(sql);
        }
        public DataTable thongKeDatHang(string tungay, string denngay, string makhncc)
        {
            //string sql = "select  N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + ThongTinSuDung.tendoanhnghiep + "' as tendoanhnghiep,N'" + ThongTinSuDung.diachi + "' as diachi,'" + ThongTinSuDung.dienthoai + "' as sodt, a.mahang,tenhang,b.dvt, sum(a.slchuaquydoi * slquydoi) as slchuaquydoi,dgnhapxuat/slquydoi, chietkhau,(1-chietkhau * 0.01) * sum(a.slchuaquydoi)*dgnhapxuat as thanhtien ";
            //sql += "from DonDatHangCT a left join HangHoa b on a.mahang = b.mahang left join DonDatHang c on a.manhapxuat = c.manhapxuat where dagiao = 'false' and ";
            //sql += " convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            //if (makhncc != "")
            //{
            //    sql += "and makhncc = '" + makhncc + "' ";
            //}
            //sql += "group by a.mahang,tenhang,b.dvt, dgnhapxuat, chietkhau";
            string sql = "select  N'Từ ngày ' + '" + tungay + "' + N' đến ngày ' + '" + denngay + "' as khoangthoigian,N'" + ThongTinSuDung.tendoanhnghiep + "' as tendoanhnghiep,N'" + ThongTinSuDung.diachi + "' as diachi,'" + ThongTinSuDung.dienthoai + "' as sodt, ngayphaigiao,  hanghoa,dvt, sum(a.slchuaquydoi - sldagiao) as slchuaquydoi ";
            sql += "from v_DDHChiTietChuaGiao a left join DonDatHang c on a.manhapxuat = c.manhapxuat where dagiao = 'false' and ";
            sql += " convert(datetime,ngaynhapxuat,103) >= convert(datetime,'" + tungay + "',103) and convert(datetime,ngaynhapxuat,103) <= convert(datetime,'" + denngay + "',103)   ";
            if (makhncc != "")
            {
                sql += "and makhncc = '" + makhncc + "' ";
            }
            sql += "group by hanghoa,dvt, ngayphaigiao order by hanghoa";
            return getDataTable(sql);
        }
        #region Report
        public DataTable inDonDatHang(string tendoanhnghiep, string diachi, string sodt, string manhapxuat)
        {
            ////string sql = "select N'" + tenreport + "' as tenreport,N'" + thongtindoanhnghiep + "' as thongtindoanhnghiep,a.manhapxuat,ngaynhapxuat,tenkhncc,diachikhncc,sodtkhncc,ghichukhncc,tenhang,b.dvt,dgnhapxuat,slchuaquydoi,thanhtien,chietkhau,tongtienso,tongtienchu,thanhtoan,nocu,tongnoso,tongnochu ";
            ////sql += "from PhieuNhapXuat a, PhieuNhapXuatCT b, KhachHangNCC c,HangHoa d ";
            ////sql += "where a.manhapxuat = '" + manhapxuat + "' and a.manhapxuat = b.manhapxuat and a.makhncc = c.makhncc and b.mahang = d.mahang ";
            ////return getDataTable(sql);
            //string[] strPara = { "@thongtindoanhnghiep", "@manhapxuat" };
            //string[] strValue = { thongtindoanhnghiep, manhapxuat };
            //return getDataTableSP("sp_inPhieuNhapXuat", strPara, strValue);
            string sql = "select distinct stt,N'ĐƠN ĐẶT HÀNG' AS tenreport ";
            sql += ",N'" + tendoanhnghiep + "' as tendoanhnghiep,N'" + diachi + "' as diachi, '" + sodt + "' as sodt,a.manhapxuat,N'Ngày ' + case when len(convert(char(2),day(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),day(convert(datetime,ngaynhapxuat,103))) else convert(char(2),day(convert(datetime,ngaynhapxuat,103))) end + N' tháng ' + case when len(convert(char(2),month(convert(datetime,ngaynhapxuat,103)))) = 1 then '0' + convert(char(2),month(convert(datetime,ngaynhapxuat,103))) else convert(char(2),month(convert(datetime,ngaynhapxuat,103))) end  + N' năm ' + convert(char(4),year(ngaynhapxuat)) as ngaynhapxuat,tenkhncc,diachikhncc,sodtkhncc,ghichunx,tenhang,b.dvt,dgnhapxuat,chuthich,convert(varchar(5),slchuaquydoi) as slchuaquydoi,quycach,thanhtien, chietkhau,tongtienso, tamung,tongtienso - tamung as conlai,tongtienchu ";
            sql += "from DonDatHang a left join DonDatHangCT b on a.manhapxuat = b.manhapxuat left join HangHoa c on b.mahang = c.mahang left join KhachHangNCC d on a.makhncc = d.makhncc ";
            sql += "where a.manhapxuat = '" + manhapxuat + "'  order by stt ";
            return getDataTable(sql);
        }

        public int layIDDonDatHangMax()
        {
            string sql = "select max(id) from PhieuNhapXuat ";
            return (int)getNumberValue(sql);
        }
        #endregion
        



    }
}
